package info.marlan.sim.util;

import info.marlan.sim.Constants;
import info.marlan.sim.R;
import info.marlan.sim.TestState;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper {

	public static final String DB_NAME = "lpic";
	public static final String DB_TABLE_QUESTION = "question";
	public static final String DB_TABLE_ANSWER = "answer";
	public static final String DB_TABLE_OBJECTIVE = "objective";
	public static final String DB_TABLE_OBJECTIVE_CATEGORY = "objective_category";
	public static final String DB_TABLE_LANGUAGE = "language";
	public static final String DB_TABLE_STATS = "stats";
	public static final int DB_VERSION = 4;

	private static final String CLASSNAME = DBHelper.class.getSimpleName();

	private static final String[] COLS_QUESTION = new String[] { "id",
			"languagecode", "category", "type", "question", "explanation",
			"freeTextAnswer", "severity" };
	private static final String[] COLS_ANSWER = new String[] { "id",
			"question", "answer", "correct", "sort" };
	private static final String[] COLS_OBJECTIVE = new String[] { "id",
			"objective_category", "title", "category", "weight", "sort" ,"languagecode","questions"};
	private static final String[] COLS_OBJECTIVE_CATEGORY = new String[] {
			"id", "title", "category", "languagecode" ,"questions"};
	private static final String[] COLS_LANGUAGE = new String[] { "id",
			"languagecode", "language" };
	
	private static final String[] COLS_STATS = new String[] { "id",
		"category", "numberquestions","correctanswers","points","maxpoints" ,"examdate"};

	private SQLiteDatabase db;
	private final DBOpenHelper dbOpenHelper;
	private final Context context;

	public static class Question implements Serializable{


		private static final long serialVersionUID = 8089472817594038740L;
		public int id;
		public String languagecode;
		public String category;
		public int type;
		public String question;
		public String explanation;
		public String freeTextAnswer;
		public String freeTextAnswerState;
		public int severity;
		public List<Answer> answers;
		public List<Answer> shuffledAnswers;

		public Question() {
			super();
		}
		
		private String getShuffeldAnswerNumber(String unshuffeldNumber){
			int number = Integer.parseInt(unshuffeldNumber);
			Answer answer = answers.get(number-1);
			for(Answer a:shuffledAnswers){
				if(a.id==answer.id){
					return ""+(shuffledAnswers.indexOf(a)+1);
				}
			}
			return "<<?>>";
		}
		
	    public String getExplanation(){
	    	if(explanation==null){
	    		return null;
	    	}
	    	Pattern p = Pattern.compile("(\\<\\<([0-9&]+)\\>\\>)");
	        Matcher m = p.matcher(explanation);
	        StringBuilder newText = new StringBuilder();
	        int offset = 0;
	        try{
	        while (m.find(offset)) {
	            newText.append(explanation.substring(offset, m.start()));
	            offset = m.end();
	            if(m.group(2).contains("&")){
	                String[] a = m.group(2).split("&");
	                for(int i=0;i<a.length;i++){
	                    if(i>0){
	                        newText.append("&");    
	                    }
	                    newText.append(getShuffeldAnswerNumber(a[i]));    
	                }
	                
	            }else{
	                newText.append(getShuffeldAnswerNumber(m.group(2)));
	            }
	            
	        }
	        }catch(Exception e){
	        	Log.w(Constants.LOGTAG,e);
	        	return "";
	        }
	        newText.append( explanation.substring(offset, explanation.length()));
	    	
	    	return newText.toString().replaceAll("<br>", "\n");
	    }
		
		public int countCorrectAnswers(){
			if(answers==null|| answers.size()==0){
				return 0;
			}
			int correctAnswers = 0;
			for(Answer a:answers){
				if(a.correct){
					correctAnswers++;
				}
			}
			return correctAnswers;
		}

		public boolean answeredCorrect(){
			if(( answers==null || answers.size()==0 ) && ( freeTextAnswer==null || "".equals(freeTextAnswer.trim()))){
				return false;
			}
			boolean c = true;
			if(freeTextAnswer==null || freeTextAnswer.length()< 1){
				for(Answer a : answers){
					if(a.correct!=a.state){
						c = false;
					}
				}
			}else{
				if( freeTextAnswerState==null ){
					c = false;
				}else if(type==Constants.QUESTION_TYPE_SORT_REGEX){
					c = freeTextAnswerState.matches(freeTextAnswer);
				}else{
					c = freeTextAnswerState.equals(freeTextAnswer);
				}
			}
			return c;
		}
	}

	
	
	public static class Objective {
		public int id;
		public int objectiveCategory;
		public String title;
		public String category;
		public int weight;
		public int sort;
		public String languagecode;
		public int questions;
		public String getShortenTitle(){
			if(title==null){
				return "";
			}
			return title.length() > 35 ? title.substring(0,35) + "..." : title;
		}
		
		public Objective() {
			super();
		}
	}

	public static class ObjectiveCategory {
		public int id;
		public String title;
		public String category;
		public String languagecode;
		public int questions;
		public String getShortenTitle(){
			if(title==null){
				return "";
			}
			return title.length() > 35 ? title.substring(0,35)  + "..." : title;
		}
		
		public ObjectiveCategory() {
			super();
		}
	}

	public static class Answer implements Serializable{

		private static final long serialVersionUID = 2202207760717872L;
		public int id;
		public int question;
		public String answer;
		public boolean correct;
		public int sort;
		public boolean state;
		public Answer() {
			super();
		}
	}

	public static class Language {
		public int id;
		public String languagecode;
		public String language;

		public Language() {
			super();
		}
	}
	
	public int[] getStats(String category){
		int[] result = new int[]{0,0,0,0}; 
		Cursor c = null;
        try {
            c = this.db.rawQuery("select sum(numberquestions),sum(correctanswers),sum(points),sum(maxpoints) " +
            		"from stats where category like '"+category.substring(0, 1)+"%'", null);
            Log.v(Constants.LOGTAG,"select sum(numberquestions),sum(correctanswers),sum(points),sum(maxpoints) " +
            		"from stats where category like '"+category.substring(0, 1)+"%'");
            if (c.getCount() <= 0) { 
            	return result; 
            }
            c.moveToFirst();
            result[0] = c.getInt(0);
            result[1] = c.getInt(1);
            result[2] = c.getInt(2);
            result[3] = c.getInt(3);
            Log.v(Constants.LOGTAG,"select sum(numberquestions),sum(correctanswers),sum(points),sum(maxpoints) " +
            		"from stats where category like '"+category.substring(0, 1)+"%'" + result[0]);
            return result;
        }
        finally {
            if (null != c) {
                try { c.close(); }
                catch (SQLException e) { }
            }
        }
	}

	public void updateStats(TestState state){
		if(state.isSaved){
			return;
		}
		ContentValues values = new ContentValues();
		values.put(COLS_STATS[1],state.name);
		values.put(COLS_STATS[2],state.questions.size());
		values.put(COLS_STATS[3], state.getCorrectAnswers());
		values.put(COLS_STATS[4], state.getPoints());
		values.put(COLS_STATS[5], state.getPossiblePoints());
		values.put(COLS_STATS[6], new Date().getTime());
		this.db.insert(DBHelper.DB_TABLE_STATS, null, values);
		state.isSaved = true;
	}

	public List<Language> getLanguages() {
		ArrayList<Language> ret = new ArrayList<Language>();
		Cursor c = null;
		try {
			c = this.db.query(DBHelper.DB_TABLE_LANGUAGE,
					DBHelper.COLS_LANGUAGE, null, null, null, null, null);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				Language language = new Language();
				language.id = c.getInt(0);
				language.languagecode = c.getString(1);
				language.language = c.getString(2);
				ret.add(language);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(Constants.LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}

	public List<ObjectiveCategory> getObjectiveCategories(String selection, String[] selectionArgs, String orderBy) {
		ArrayList<ObjectiveCategory> ret = new ArrayList<ObjectiveCategory>();
		Cursor c = null;
		try {
			c = this.db.query(DBHelper.DB_TABLE_OBJECTIVE_CATEGORY,
					DBHelper.COLS_OBJECTIVE_CATEGORY, selection,  selectionArgs, null, null,
					orderBy);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				ObjectiveCategory objectiveCategory = new ObjectiveCategory();
				objectiveCategory.id = c.getInt(0);
				objectiveCategory.title = c.getString(1);
				objectiveCategory.category = c.getString(2);
				objectiveCategory.languagecode = c.getString(3);
				objectiveCategory.questions = c.getInt(4);

				ret.add(objectiveCategory);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(Constants.LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}

	public List<Objective> getObjective(String selection, String[] selectionArgs, String orderBy) {
		ArrayList<Objective> ret = new ArrayList<Objective>();
		Cursor c = null;
		try {
			c = this.db.query(DBHelper.DB_TABLE_OBJECTIVE,
					DBHelper.COLS_OBJECTIVE, selection, selectionArgs, null, null, orderBy);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				Objective objective = new Objective();
				objective.id = c.getInt(0);
				objective.objectiveCategory = c.getInt(1);
				objective.title = c.getString(2);
				objective.category = c.getString(3);
				objective.weight = c.getInt(4);
				objective.sort = c.getInt(5);
				objective.languagecode = c.getString(6);
				objective.questions = c.getInt(7);
				ret.add(objective);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(Constants.LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}

	public List<Question> getQuestions(String selection, String[] selectionArgs, String orderBy, String limit) {
		ArrayList<Question> ret = new ArrayList<Question>();
		Cursor c = null;
		try {
			c = this.db.query(DBHelper.DB_TABLE_QUESTION,
					DBHelper.COLS_QUESTION, selection, selectionArgs, null, null,orderBy,limit);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				Question question = new Question();
				question.id = c.getInt(0);
				question.languagecode = c.getString(1);
				question.category = c.getString(2);
				question.type = c.getInt(3);
				question.question = c.getString(4);
				question.explanation = c.getString(5);
				question.freeTextAnswer = c.getString(6);
				question.severity = c.getInt(7);
				question.answers = getAnswers(question);
				if(question.answers !=null && question.answers.size()>0){
					question.shuffledAnswers = new ArrayList<Answer>();
					question.shuffledAnswers.addAll(question.answers);
					Collections.shuffle(question.shuffledAnswers);
				}
				ret.add(question);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(Constants.LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}

	public List<Answer> getAnswers(Question question) {
		ArrayList<Answer> ret = new ArrayList<Answer>();
		Cursor c = null;
		try {
			c = this.db.query(DBHelper.DB_TABLE_ANSWER, DBHelper.COLS_ANSWER,
					"question=?", new String[]{""+question.id}, null, null, null);
			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				Answer answer = new Answer();
				answer.id = c.getInt(0);
				answer.question = c.getInt(1);
				answer.answer = c.getString(2);
				answer.correct = c.getInt(3) == 0 ? false : true;
				answer.sort = c.getInt(4);

				ret.add(answer);
				c.moveToNext();
			}
		} catch (SQLException e) {
			Log.v(Constants.LOGTAG, DBHelper.CLASSNAME, e);
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
		return ret;
	}

	private static class DBOpenHelper extends SQLiteOpenHelper {
		private final Context context;

		public DBOpenHelper(final Context context) {
			super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(final SQLiteDatabase db) {
			Log.v(Constants.LOGTAG,"DBOpenHelper#onCreate called");
			String[] sql = context.getString(R.string.create_db).split("\n");
			try {
				for (String s : sql) {
					if (s.trim().length() > 0) {
						db.execSQL(s);
					}
				}
				
				fillDB(db,R.raw.answer);
				fillDB(db,R.raw.objective);
				fillDB(db,R.raw.question);
			} catch (SQLException e) {
				Log.e(Constants.LOGTAG, DBHelper.CLASSNAME, e);
			}
		}


		@Override
		public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
				final int newVersion) {
			
			String[] sql = context.getString(R.string.upgrade_db).split("\n");
			try {
				for (String s : sql) {
					if (s.trim().length() > 0) {
						Log.v(Constants.LOGTAG, s);
						db.execSQL(s);
					}
				}
			} catch (SQLException e) {
				Log.e(Constants.LOGTAG, DBHelper.CLASSNAME, e);
			}
			//this.onCreate(db);
		}
	
		
		private void fillDB(final SQLiteDatabase db, int resource) {
				
			   BufferedReader in = null;
		        try {
		            in = new BufferedReader(
		            		new InputStreamReader(
		            		context.getResources().openRawResource(resource),"UTF8" ));
		            String line;
		            while ((line = in.readLine()) != null){
		            	if (line.trim().length() > 0) {
		            		Log.v(Constants.LOGTAG, line);
		            	db.execSQL(line);
		            	}
		            }
		            
		        } catch (IOException e) {
		        	Log.e(Constants.LOGTAG, DBHelper.CLASSNAME, e);
		        } finally {
		        	if (in != null) {
		                try {
		                    in.close();
		                } catch (IOException e) {
		                    // Ignore
		                }
		            }

		        }
		}
	}

	//
	// end inner classes
	//

	public DBHelper(final Context context) {
		this.dbOpenHelper = new DBOpenHelper(context);
		this.establishDb();
		this.context = context;
	}

	private void establishDb() {
		if (this.db == null) {
			this.db = this.dbOpenHelper.getWritableDatabase();
		}
	}

	public void cleanup() {
		if (this.db != null) {
			this.db.close();
			this.db = null;
		}
	}


}